unit Unit42; 

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs, Menus,
  sqldb, db, DBGrids, Unit1, DateUtils, Unit22, Unit43, LR_DBSet, LR_Class, Unit55;

type

  { TForm42 }

  TForm42 = class(TForm)
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    frDBDataSet1: TfrDBDataSet;
    frReport1: TfrReport;
    MainMenu1: TMainMenu;
    MenuItem1: TMenuItem;
    MenuItem10: TMenuItem;
    MenuItem11: TMenuItem;
    MenuItem2: TMenuItem;
    MenuItem3: TMenuItem;
    MenuItem4: TMenuItem;
    MenuItem5: TMenuItem;
    MenuItem6: TMenuItem;
    MenuItem7: TMenuItem;
    MenuItem8: TMenuItem;
    MenuItem9: TMenuItem;
    SQLQuery1: TSQLQuery;
    SQLQuery2: TSQLQuery;
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure frReport1GetValue(const ParName: String; var ParValue: Variant);
    procedure MenuItem10Click(Sender: TObject);
    procedure MenuItem11Click(Sender: TObject);
    procedure MenuItem2Click(Sender: TObject);
    procedure MenuItem4Click(Sender: TObject);
    procedure MenuItem5Click(Sender: TObject);
    procedure MenuItem7Click(Sender: TObject);
    procedure MenuItem8Click(Sender: TObject);
    procedure MenuItem9Click(Sender: TObject);
    procedure ordenar_grid();
    procedure FormShow();
  private
    cliente : string;
    nombre_sucursal : string;
    fecha_desde : string;
    fecha_hasta : string;
  public
    { public declarations }
  end; 

var
  Form42: TForm42; 

implementation

{ TForm42 }

procedure TForm42.ordenar_grid();
begin;
  if SQLQuery1.Active = True then
    SQLQuery1.Close;

  SQLQuery1.SQL.Text := 'select * from vw_pago where momento >= ''' + fecha_desde
    + ''' and momento <= ''' + fecha_hasta + ''' and cln_nombre ilike ''%' + cliente
    + '%'' and suc_nombre ilike ''%' + nombre_sucursal + '%'' order by momento, pgo_cdg';

  SQLQuery1.Open;

  DBGrid1.Columns[0].Title.Caption := 'Código';
  DBGrid1.Columns[2].Title.Caption := 'Fecha';
  DBGrid1.Columns[3].Title.Caption := 'Hora';
  DBGrid1.Columns[5].Title.Caption := 'Cliente';
  DBGrid1.Columns[6].Title.Caption := 'Sucursal';
  DBGrid1.Columns[7].Title.Caption := 'Monto';
  DBGrid1.Columns[8].Title.Caption := 'Tipo';
  DBGrid1.Columns[9].Title.Caption := 'Descripción';

  DBGrid1.Columns[1].Visible:= False;
  DBGrid1.Columns[4].Visible:= False;

  DBGrid1.Columns[0].Width := 60;
  DBGrid1.Columns[2].Width := 100;
  DBGrid1.Columns[3].Width := 60;
  DBGrid1.Columns[5].Width := 150;
  DBGrid1.Columns[6].Width := 100;
  DBGrid1.Columns[7].Width := 65;
  DBGrid1.Columns[8].Width := 100;
  DBGrid1.Columns[9].Width := 200;
  
  TColumn(DBGrid1.Columns[5]).DisplayFormat := '$ ####.00';
end;

procedure TForm42.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  if SQLQuery1.Active = True then
    SQLQuery1.Close;
end;

procedure TForm42.FormCreate(Sender: TObject);
begin
  if SQLQuery1.Active = True then
    SQLQuery1.Close;

  if SQLQuery2.Active = True then
    SQLQuery2.Close;

  SQLQuery1.DataBase := Form1.PQConnection1;
  SQLQuery1.Transaction := Form1.SQLTransaction1;

  SQLQuery2.DataBase := Form1.PQConnection1;
  SQLQuery2.Transaction := Form1.SQLTransaction1;
end;

procedure TForm42.frReport1GetValue(const ParName: String; var ParValue: Variant
  );
begin
  if ParName = 'fecha_desde' then
    ParValue := fecha_desde;
    
  if ParName = 'fecha_hasta' then
    ParValue := fecha_hasta;
end;

procedure TForm42.MenuItem10Click(Sender: TObject);
begin
  frReport1.LoadFromFile('listado_pagos.lrf');
  frReport1.ShowReport;
end;

procedure TForm42.MenuItem11Click(Sender: TObject);
begin
  Form55.ShowModal;
  
  nombre_sucursal := Form55.nombre_sucursal;
  
  ordenar_grid;
end;

procedure TForm42.MenuItem2Click(Sender: TObject);
begin
  Close;
end;

procedure TForm42.MenuItem4Click(Sender: TObject);
begin
  Form43.ShowModal;
  FormShow;
end;

procedure TForm42.MenuItem5Click(Sender: TObject);
var
  QueryString : string;
  codigo : string;
  codigo_cliente : string;
  codigo_venta : string;
  monto_pago : real;
  monto_pago_old : real;
  saldo_venta : real;
  monto_venta : real;
  saldo_cliente : real;
  
begin
  codigo := InputBox('Cancelar Pago', 'Ingrese el código de pago a cancelar:', '');

  if codigo <> '' then
  begin
    if SQLQuery2.Active = true then
      SQLQuery2.Close;

    SQLQuery2.SQL.Text := 'select pgo_cliente, pgo_monto from pago where pgo_cdg = ' + codigo;

    SQLQuery2.Open;

    while not SQLQuery2.EOF do
    begin
      codigo_cliente := SQLQuery2.Fields[0].AsString;
      monto_pago := SQLQuery2.Fields[1].AsFloat;
      SQLQuery2.Next;
    end;

    monto_pago_old := monto_pago;

    SQLQuery2.Close;

    QueryString := 'delete from pago where pgo_cdg = ' + codigo;

    Form1.PQConnection1.ExecuteDirect('begin work;');
    Form1.PQConnection1.ExecuteDirect(QueryString);
    Form1.PQConnection1.ExecuteDirect('commit work;');

    SQLQuery2.SQL.Text := 'select saldo from vw_cliente where cln_cdg = ' + codigo_cliente;

    SQLQuery2.Open;

    while not SQLQuery2.EOF do
    begin
      saldo_cliente := SQLQuery2.Fields[0].AsFloat;
      SQLQuery2.Next;
    end;

    SQLQuery2.Close;

    if saldo_cliente > 0 then
    begin
      while monto_pago > 0 do
      begin
        SQLQuery2.SQL.Text := 'select max(vta_cdg) from venta where vta_saldo < vta_total '
          + 'and vta_tipo = ''CTACTE'' and vta_cliente = ' + codigo_cliente;

        SQLQuery2.Open;

        while not SQLQuery2.EOF do
        begin
          codigo_venta := SQLQuery2.Fields[0].AsString;
          SQLQuery2.Next;
        end;

        SQLQuery2.Close;

        saldo_venta := 0;
        monto_venta := 0;

        SQLQuery2.SQL.Text := 'select vta_total, vta_saldo from venta where vta_cdg = ' + codigo_venta;

        SQLQuery2.Open;

        while not SQLQuery2.EOF do
        begin
          monto_venta := SQLQuery2.Fields[0].AsFloat;
          saldo_venta := SQLQuery2.Fields[1].AsFloat;
          SQLQuery2.Next;
        end;

        SQLQuery2.Close;

        if (monto_venta - saldo_venta) >= monto_pago then
        begin
          saldo_venta += monto_pago;
          monto_pago := 0;

          QueryString := 'update venta set vta_saldo = ' + floattostr(saldo_venta) + ' where vta_cdg = ' + codigo_venta;
        end
        else
        begin
          monto_pago -= (monto_venta - saldo_venta);
          saldo_venta := monto_venta;

          QueryString := 'update venta set vta_saldo = ' + floattostr(saldo_venta) + ' where vta_cdg = ' + codigo_venta;
        end;

          Form1.PQConnection1.ExecuteDirect('begin work;');
          Form1.PQConnection1.ExecuteDirect(QueryString);
          Form1.PQConnection1.ExecuteDirect('commit work;');
      end;
    end;

    QueryString := 'insert into resumen_cuenta(rsm_cliente, rsm_descripcion, rsm_operacion, rsm_debe) values('
      + codigo_cliente + ', ''CANCELACION PAGO'', ' + codigo + ', ' + floattostr(monto_pago_old) + ')';

    Form1.PQConnection1.ExecuteDirect('begin work;');
    Form1.PQConnection1.ExecuteDirect(QueryString);
    Form1.PQConnection1.ExecuteDirect('commit work;');

    ShowMessage('Cancelación Exitosa');
    ordenar_grid();
  end;
end;



procedure TForm42.MenuItem7Click(Sender: TObject);
begin
  Form22.Showmodal;

  fecha_desde := Form22.fecha_desde;
  fecha_hasta := Form22.fecha_hasta;

  ordenar_grid;
end;

procedure TForm42.MenuItem8Click(Sender: TObject);
begin
  cliente := InputBox('Filtro por Cliente', 'Ingrese parte del nombre del cliente:', '');

  if cliente <> '' then
    ordenar_grid;
end;

procedure TForm42.MenuItem9Click(Sender: TObject);
begin
  FormShow;
end;

procedure TForm42.FormShow();
begin
  cliente := '%';
  fecha_desde := DateToStr(Date) + ' ' + '00:00';
  fecha_hasta := DateToStr(Date) + ' ' + '23:59';
  nombre_sucursal := Form1.mi_sucursal;
  ordenar_grid;
end;

initialization
  {$I unit42.lrs}

end.

